{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import datetime as dt\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas.plotting import scatter_matrix\n",
    "from sklearn.preprocessing import OneHotEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "churn_eng = pd.read_csv(\"../data/df_from_eda.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id</th>\n",
       "      <th>date</th>\n",
       "      <th>gender</th>\n",
       "      <th>vertical</th>\n",
       "      <th>deposit_approved_sum</th>\n",
       "      <th>withdrawal_approved_sum</th>\n",
       "      <th>turnover_cash_sum</th>\n",
       "      <th>winning_cash_sum</th>\n",
       "      <th>result_cash_sum</th>\n",
       "      <th>NGR_sum</th>\n",
       "      <th>bonus_cash_sum</th>\n",
       "      <th>session_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0004fd6b7433920d183e818e18efb97c1b02afb3</td>\n",
       "      <td>2013-01-21</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0004fd6b7433920d183e818e18efb97c1b02afb3</td>\n",
       "      <td>2013-01-22</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0004fd6b7433920d183e818e18efb97c1b02afb3</td>\n",
       "      <td>2013-01-29</td>\n",
       "      <td>female</td>\n",
       "      <td>casino_classic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>43</td>\n",
       "      <td>-30</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>2313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  player_id        date  gender  \\\n",
       "0  0004fd6b7433920d183e818e18efb97c1b02afb3  2013-01-21  female   \n",
       "1  0004fd6b7433920d183e818e18efb97c1b02afb3  2013-01-22  female   \n",
       "2  0004fd6b7433920d183e818e18efb97c1b02afb3  2013-01-29  female   \n",
       "\n",
       "         vertical  deposit_approved_sum  withdrawal_approved_sum  \\\n",
       "0             NaN                    31                        0   \n",
       "1             NaN                     0                        0   \n",
       "2  casino_classic                     0                        0   \n",
       "\n",
       "   turnover_cash_sum  winning_cash_sum  result_cash_sum  NGR_sum  \\\n",
       "0                  0                 0                0        0   \n",
       "1                  0                 0                0        0   \n",
       "2                 73                43              -30       30   \n",
       "\n",
       "   bonus_cash_sum  session_sum  \n",
       "0               0         2233  \n",
       "1               0         1957  \n",
       "2               0         2313  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "churn_eng.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert `date`column to datetime format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert object column to datetime format\n",
    "churn_eng[\"date\"] = pd.to_datetime(churn_eng[\"date\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create 4 new columns from `date`:\n",
    "* `day_of_the_week`\n",
    "* `day_of_the_month`\n",
    "* `month`\n",
    "* `week_of_year`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new features from date\n",
    "# churn_eng[\"day_of_week\"] = churn_eng[\"date\"].dt.weekday  # day of the week (0 = Monday)\n",
    "# churn_eng[\"day_of_month\"] = churn_eng[\"date\"].dt.day  # day of the month\n",
    "# churn_eng[\"month\"] = churn_eng[\"date\"].dt.month  # month\n",
    "# churn_eng[\"week_of_year\"] = churn_eng[\"date\"].dt.isocalendar().week  # week of the year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The format of the feature `player_id` is not useful neither practical. So it will be modified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert player_id to category to label enconder it\n",
    "churn_eng[\"player_id\"] = churn_eng[\"player_id\"].astype(\"category\")\n",
    "churn_eng[\"player_id_cat\"] = churn_eng[\"player_id\"].cat.codes\n",
    "churn_eng[\"player_id_cat\"] = churn_eng[\"player_id_cat\"] + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing features\n",
    "\n",
    "We can remove `player_id` now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing features\n",
    "# churn_eng = churn_eng.drop([\"player_id\", \"date\"], axis=1)\n",
    "churn_eng = churn_eng.drop([\"player_id\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the missing values are less than 1% for `gender` and about 3% for `vertical` they will be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# it will be used in a naive definition of the target variable\n",
    "churn_naive = churn_eng.copy().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove row with missing value for gender and vertical\n",
    "churn_eng.dropna(axis=0, subset=[\"gender\"], inplace=True)\n",
    "churn_eng.dropna(axis=0, subset=[\"vertical\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Features with missing values:\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total number of missing values, if they are present\n",
    "print(\"Features with missing values:\\n\")\n",
    "churn_eng.isnull().sum()[churn_eng.isnull().sum() > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No more missing values!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dimensions of the dataset:\n",
      " Number of rows: 112851\n",
      " Number of columns: 12\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# showing new dimensions of the dataset\n",
    "print(\"Dimensions of the dataset:\")\n",
    "print(f\" Number of rows: {churn_eng.shape[0]}\")\n",
    "print(f\" Number of columns: {churn_eng.shape[1]}\")\n",
    "print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reordering columns\n",
    "\n",
    "For the sake of clarity, the order of the columns in the dataframe will be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['date',\n",
       " 'gender',\n",
       " 'vertical',\n",
       " 'deposit_approved_sum',\n",
       " 'withdrawal_approved_sum',\n",
       " 'turnover_cash_sum',\n",
       " 'winning_cash_sum',\n",
       " 'result_cash_sum',\n",
       " 'NGR_sum',\n",
       " 'bonus_cash_sum',\n",
       " 'session_sum',\n",
       " 'player_id_cat']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# actual order of columns\n",
    "list(churn_eng.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a list with the new order for the columns\n",
    "cols_new_order = [\n",
    "    \"player_id_cat\",\n",
    "    \"gender\",\n",
    "    \"vertical\",\n",
    "    \"date\",\n",
    "    #    \"day_of_week\",\n",
    "    #    \"day_of_month\",\n",
    "    #    \"month\",\n",
    "    #    \"week_of_year\",\n",
    "    \"deposit_approved_sum\",\n",
    "    \"withdrawal_approved_sum\",\n",
    "    \"turnover_cash_sum\",\n",
    "    \"winning_cash_sum\",\n",
    "    \"result_cash_sum\",\n",
    "    \"NGR_sum\",\n",
    "    \"bonus_cash_sum\",\n",
    "    \"session_sum\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reordereing columns\n",
    "churn_eng = churn_eng.reindex(columns=cols_new_order)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id_cat</th>\n",
       "      <th>gender</th>\n",
       "      <th>vertical</th>\n",
       "      <th>date</th>\n",
       "      <th>deposit_approved_sum</th>\n",
       "      <th>withdrawal_approved_sum</th>\n",
       "      <th>turnover_cash_sum</th>\n",
       "      <th>winning_cash_sum</th>\n",
       "      <th>result_cash_sum</th>\n",
       "      <th>NGR_sum</th>\n",
       "      <th>bonus_cash_sum</th>\n",
       "      <th>session_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>casino_classic</td>\n",
       "      <td>2013-01-29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>43</td>\n",
       "      <td>-30</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>2313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>casino_classic</td>\n",
       "      <td>2013-02-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>casino_classic</td>\n",
       "      <td>2013-02-18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_id_cat  gender        vertical       date  deposit_approved_sum  \\\n",
       "2              1  female  casino_classic 2013-01-29                     0   \n",
       "3              1  female  casino_classic 2013-02-07                     0   \n",
       "4              1  female  casino_classic 2013-02-18                     0   \n",
       "\n",
       "   withdrawal_approved_sum  turnover_cash_sum  winning_cash_sum  \\\n",
       "2                        0                 73                43   \n",
       "3                        0                  0                 0   \n",
       "4                        0                  0                 0   \n",
       "\n",
       "   result_cash_sum  NGR_sum  bonus_cash_sum  session_sum  \n",
       "2              -30       30               0         2313  \n",
       "3                0        0               0         6007  \n",
       "4                0        0               0         3601  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "churn_eng.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plt.scatter(churn_eng[\"player_id_cat\"], churn_eng[\"session_sum\"])\n",
    "# plt.show()\n",
    "# plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plt.scatter(churn_eng[\"player_id_cat\"], churn_eng[\"result_cash_sum\"])\n",
    "# plt.show()\n",
    "# plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plt.scatter(churn_eng[\"player_id_cat\"], churn_eng[\"deposit_approved_sum\"])\n",
    "# plt.show()\n",
    "# plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plt.scatter(churn_eng[\"player_id_cat\"], churn_eng[\"NGR_sum\"])\n",
    "# plt.show()\n",
    "# plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe to file\n",
    "churn_eng.to_csv(\"../data/df_from_eng.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting only `casino_classic` gamblers for the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "casino_classic    0.712665\n",
       "sports            0.157960\n",
       "casino_live       0.101576\n",
       "mixture           0.027798\n",
       "Name: vertical, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groups of gamblers in %\n",
    "churn_eng.vertical.value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the four groups of gamblers contained in the dataset the `casino_classic` counts for 71.3%. Considering this and the fact that different groups of gamblers can have different behavior, we will only model here the `casino_classic` group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a copy of the complere dataset\n",
    "casino_class = churn_eng.copy().reset_index(drop=True)\n",
    "\n",
    "# filter only `casino_classic` gamblers\n",
    "casino_class = casino_class[casino_class[\"vertical\"] == \"casino_classic\"]\n",
    "\n",
    "# drop vertical column (all dataset is casino_classic now)\n",
    "casino_class = casino_class.drop([\"vertical\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((112851, 12), (80425, 11))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# comparing dimensions between the complete and the filtered dataset\n",
    "churn_eng.shape, casino_class.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id_cat</th>\n",
       "      <th>gender</th>\n",
       "      <th>date</th>\n",
       "      <th>deposit_approved_sum</th>\n",
       "      <th>withdrawal_approved_sum</th>\n",
       "      <th>turnover_cash_sum</th>\n",
       "      <th>winning_cash_sum</th>\n",
       "      <th>result_cash_sum</th>\n",
       "      <th>NGR_sum</th>\n",
       "      <th>bonus_cash_sum</th>\n",
       "      <th>session_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-01-29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>43</td>\n",
       "      <td>-30</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>2313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-02-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-02-18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-03-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-03-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_id_cat  gender       date  deposit_approved_sum  \\\n",
       "0              1  female 2013-01-29                     0   \n",
       "1              1  female 2013-02-07                     0   \n",
       "2              1  female 2013-02-18                     0   \n",
       "3              1  female 2013-03-06                     0   \n",
       "4              1  female 2013-03-07                     0   \n",
       "\n",
       "   withdrawal_approved_sum  turnover_cash_sum  winning_cash_sum  \\\n",
       "0                        0                 73                43   \n",
       "1                        0                  0                 0   \n",
       "2                        0                  0                 0   \n",
       "3                        0                  0                 0   \n",
       "4                        0                  0                 0   \n",
       "\n",
       "   result_cash_sum  NGR_sum  bonus_cash_sum  session_sum  \n",
       "0              -30       30               0         2313  \n",
       "1                0        0               0         6007  \n",
       "2                0        0               0         3601  \n",
       "3                0        0               0         2099  \n",
       "4                0        0               0         4256  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "casino_class.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe to file\n",
    "casino_class.to_csv(\"../data/df_from_casino_classic.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping by player_id and gender\n",
    "\n",
    "Since each player can be represented by more than one data point we'll group them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns\n",
    "cols = [\n",
    "    \"deposit_approved_sum\",\n",
    "    \"withdrawal_approved_sum\",\n",
    "    \"turnover_cash_sum\",\n",
    "    \"winning_cash_sum\",\n",
    "    \"result_cash_sum\",\n",
    "    \"NGR_sum\",\n",
    "    \"bonus_cash_sum\",\n",
    "    \"session_sum\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by player_id and gender\n",
    "casino_class_grp = (\n",
    "    casino_class.groupby([\"player_id_cat\", \"gender\"])[cols].sum().reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id_cat</th>\n",
       "      <th>gender</th>\n",
       "      <th>deposit_approved_sum</th>\n",
       "      <th>withdrawal_approved_sum</th>\n",
       "      <th>turnover_cash_sum</th>\n",
       "      <th>winning_cash_sum</th>\n",
       "      <th>result_cash_sum</th>\n",
       "      <th>NGR_sum</th>\n",
       "      <th>bonus_cash_sum</th>\n",
       "      <th>session_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>87</td>\n",
       "      <td>146</td>\n",
       "      <td>309</td>\n",
       "      <td>337</td>\n",
       "      <td>28</td>\n",
       "      <td>-29</td>\n",
       "      <td>106</td>\n",
       "      <td>81819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>male</td>\n",
       "      <td>1915</td>\n",
       "      <td>109</td>\n",
       "      <td>5003</td>\n",
       "      <td>3197</td>\n",
       "      <td>-1806</td>\n",
       "      <td>1785</td>\n",
       "      <td>98</td>\n",
       "      <td>28623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>male</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>280</td>\n",
       "      <td>189</td>\n",
       "      <td>-91</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>5468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>female</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>103</td>\n",
       "      <td>45</td>\n",
       "      <td>-58</td>\n",
       "      <td>59</td>\n",
       "      <td>41</td>\n",
       "      <td>2122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>male</td>\n",
       "      <td>1885</td>\n",
       "      <td>1345</td>\n",
       "      <td>8751</td>\n",
       "      <td>8036</td>\n",
       "      <td>-715</td>\n",
       "      <td>513</td>\n",
       "      <td>69</td>\n",
       "      <td>23072</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_id_cat  gender  deposit_approved_sum  withdrawal_approved_sum  \\\n",
       "0              1  female                    87                      146   \n",
       "1              2    male                  1915                      109   \n",
       "2              4    male                    91                        0   \n",
       "3              8  female                    59                        0   \n",
       "4              9    male                  1885                     1345   \n",
       "\n",
       "   turnover_cash_sum  winning_cash_sum  result_cash_sum  NGR_sum  \\\n",
       "0                309               337               28      -29   \n",
       "1               5003              3197            -1806     1785   \n",
       "2                280               189              -91       90   \n",
       "3                103                45              -58       59   \n",
       "4               8751              8036             -715      513   \n",
       "\n",
       "   bonus_cash_sum  session_sum  \n",
       "0             106        81819  \n",
       "1              98        28623  \n",
       "2               0         5468  \n",
       "3              41         2122  \n",
       "4              69        23072  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "casino_class_grp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One-hot enconding gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the encoder\n",
    "onehot = OneHotEncoder()\n",
    "\n",
    "# encode the data\n",
    "onehot_results = onehot.fit_transform(casino_class_grp[[\"gender\"]])\n",
    "\n",
    "# transform to pandas and join dataframes\n",
    "casino_class_grp = casino_class_grp.join(\n",
    "    pd.DataFrame(\n",
    "        onehot_results.toarray(), columns=[\"female\", \"male\"]\n",
    "    )  # female=1, male=2\n",
    ")\n",
    "\n",
    "# drop gender column\n",
    "casino_class_grp = casino_class_grp.drop([\"gender\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6808, 11)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show dimensions\n",
    "casino_class_grp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe to file\n",
    "casino_class_grp.to_csv(\"../data/df_from_casino_class_grp.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Naive definition of the target variable\n",
    "\n",
    "Steps:\n",
    "* calculate the time difference in days between two sucessive events for each unique player\n",
    "* keep only the last event containing the number of days for each player\n",
    "* define an interval of time (time_lapse) below which we'll consider as `no_churn`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate time difference between two sucessive dates for each unique identifier (player_id)\n",
    "churn_naive[\"time_lapse_days\"] = (\n",
    "    churn_naive.groupby(\"player_id_cat\")[\"date\"]\n",
    "    .diff()\n",
    "    .apply(lambda x: x / np.timedelta64(1, \"D\"))\n",
    "    .fillna(0)\n",
    "    .astype(\"int64\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep only the last event for each unique ientifier (player_id)\n",
    "churn_naive.drop_duplicates(subset=\"player_id_cat\", keep=\"last\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove row with missing value for gender and vertical\n",
    "churn_naive.dropna(axis=0, subset=[\"vertical\"], inplace=True)\n",
    "churn_naive.dropna(axis=0, subset=[\"gender\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a copy of the complere dataset\n",
    "casino_class_naive = churn_naive.copy().reset_index(drop=True)\n",
    "\n",
    "# filter only `casino_classic` gamblers\n",
    "casino_class_naive = casino_class_naive[\n",
    "    casino_class_naive[\"vertical\"] == \"casino_classic\"\n",
    "]\n",
    "\n",
    "# drop vertical column (all dataset is casino_classic now)\n",
    "casino_class_naive = casino_class_naive.drop([\"vertical\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set the the time lapse in days below which we consider as a no_churn\n",
    "time_lapse = 10\n",
    "casino_class_naive[\"no_churn\"] = np.where(\n",
    "    casino_class_naive[\"time_lapse_days\"] < time_lapse, 1, 0\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    0.746673\n",
       "0    0.253327\n",
       "Name: no_churn, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# percentage of positive and negative class\n",
    "casino_class_naive[\"no_churn\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# one-hot encoder the gender\n",
    "casino_class_naive = pd.get_dummies(casino_class_naive, columns=[\"gender\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# visualize time lapse distribution\n",
    "#plt.hist(casino_class_naive.time_lapse_days)\n",
    "#plt.show()\n",
    "#plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# visualize time lapse\n",
    "#plt.figure(figsize=(14,10))\n",
    "#plt.bar(casino_class_naive.player_id_cat, casino_class_naive.time_lapse_days)\n",
    "#plt.show()\n",
    "#plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# visualize player_id versus time lapse\n",
    "#plt.scatter(casino_class_naive[\"player_id_cat\"], casino_class_naive[\"time_lapse_days\"])\n",
    "#plt.show()\n",
    "#plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop features\n",
    "casino_class_naive = casino_class_naive.drop([\"date\"], axis=1)\n",
    "casino_class_naive = casino_class_naive.drop([\"time_lapse_days\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_new_order = [\n",
    "    \"player_id_cat\",\n",
    "    \"deposit_approved_sum\",\n",
    "    \"withdrawal_approved_sum\",\n",
    "    \"turnover_cash_sum\",\n",
    "    \"winning_cash_sum\",\n",
    "    \"result_cash_sum\",\n",
    "    \"NGR_sum\",\n",
    "    \"bonus_cash_sum\",\n",
    "    \"session_sum\",\n",
    "    \"gender_female\",\n",
    "    \"gender_male\",\n",
    "    \"no_churn\"\n",
    "]\n",
    "\n",
    "# reordereing columns\n",
    "casino_class_naive = casino_class_naive.reindex(columns=cols_new_order)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id_cat</th>\n",
       "      <th>deposit_approved_sum</th>\n",
       "      <th>withdrawal_approved_sum</th>\n",
       "      <th>turnover_cash_sum</th>\n",
       "      <th>winning_cash_sum</th>\n",
       "      <th>result_cash_sum</th>\n",
       "      <th>NGR_sum</th>\n",
       "      <th>bonus_cash_sum</th>\n",
       "      <th>session_sum</th>\n",
       "      <th>gender_female</th>\n",
       "      <th>gender_male</th>\n",
       "      <th>no_churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>1876</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>103</td>\n",
       "      <td>13</td>\n",
       "      <td>-90</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>463</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>280</td>\n",
       "      <td>189</td>\n",
       "      <td>-91</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>5468</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>103</td>\n",
       "      <td>45</td>\n",
       "      <td>-58</td>\n",
       "      <td>59</td>\n",
       "      <td>41</td>\n",
       "      <td>2122</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>351</td>\n",
       "      <td>527</td>\n",
       "      <td>1234</td>\n",
       "      <td>1410</td>\n",
       "      <td>176</td>\n",
       "      <td>-186</td>\n",
       "      <td>0</td>\n",
       "      <td>3396</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_id_cat  deposit_approved_sum  withdrawal_approved_sum  \\\n",
       "0              1                     0                        0   \n",
       "1              2                    90                        0   \n",
       "2              4                    91                        0   \n",
       "3              8                    59                        0   \n",
       "4              9                   351                      527   \n",
       "\n",
       "   turnover_cash_sum  winning_cash_sum  result_cash_sum  NGR_sum  \\\n",
       "0                  0                 0                0        0   \n",
       "1                103                13              -90       90   \n",
       "2                280               189              -91       90   \n",
       "3                103                45              -58       59   \n",
       "4               1234              1410              176     -186   \n",
       "\n",
       "   bonus_cash_sum  session_sum  gender_female  gender_male  no_churn  \n",
       "0              27         1876              1            0         1  \n",
       "1               0          463              0            1         0  \n",
       "2               0         5468              0            1         1  \n",
       "3              41         2122              1            0         1  \n",
       "4               0         3396              0            1         1  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "casino_class_naive.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe to file\n",
    "casino_class_naive.to_csv(\"../data/train_naive.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
